#!/bin/bash
if [ $# -lt 1 ]
then
	echo "必须至少传入一个表名/all..."
	exit
fi

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
dws_traffic_user_course_videoplay_1d="
use edu;
set hive.exec.dynamic.partition.mode=nonstrict;

insert overwrite table dws_traffic_user_course_videoplay_1d partition(dt)
select uid,
       course_name,
       if(finish_chapter_num >= cast(chapter_num as bigint),1,0) finish_course,
       dt
from (
         select course_id,
                course_name,
                uid,
                chapter_num,
                sum(is_finish) finish_chapter_num,
                dt
         from (
                  select uid,
                         id,
                         video_name,
                         course_id,
                         course_name,
                         chapter_id,
                         chapter_name,
                         chapter_num,
                         during_sec,
                         if(sum_play_sec >= during_sec * 0.9 and max_position_sec >= during_sec * 0.9, 1, 0) is_finish,
                         dt
                  from (
                           select uid,
                                  id,
                                  video_name,
                                  course_id,
                                  course_name,
                                  chapter_id,
                                  chapter_name,
                                  chapter_num,
                                  during_sec,
                                  sum(play_sec)     sum_play_sec,
                                  sum(position_sec) max_position_sec,
                                  dt
                           from dwd_videoplay_full
		       where dt<='2022-06-14'
                           group by uid, id, video_name, course_id, course_name, chapter_id, chapter_name, chapter_num,
                                    during_sec,dt
                       ) t1
              ) t2
         group by course_id, course_name, chapter_num, uid,dt
     ) t3;
"
dws_traffic_user_chapter_videoplay_1d="
use edu;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_traffic_user_chapter_videoplay_1d partition (dt )
select uid,
       id,
       video_name,
       course_id,
       course_name,
       chapter_id,
       chapter_name,
       chapter_num,
       during_sec,
       if(sum_play_sec >= during_sec * 0.9 and max_position_sec >= during_sec * 0.9, 1, 0) is_finish,
       dt
from (
         select uid,
                id,
                video_name,
                course_id,
                course_name,
                chapter_id,
                chapter_name,
                chapter_num,
                during_sec,
                sum(play_sec)     sum_play_sec,
                sum(position_sec) max_position_sec,
                dt
         from dwd_videoplay_full
        where dt<='2022-06-14'
         group by uid, id, video_name, course_id, course_name, chapter_id, chapter_name, chapter_num, during_sec,dt
     ) t1;
"

dws_play_user_course_count_1d_sql="set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_play_user_course_count_1d partition (dt)
select
      uid,
       id,
      chapter_name,
       course_name,
       count(1)  play_count_1d,
       avg(play_sec) avg_duration_sec_1d,
       count(distinct uid) uv_count_1d,
        dt
from dwd_videoplay_full
where dt <='2022-06-14'
group by course_name,uid,id,dt,chapter_name;"

dws_play_user_chapter_count_1d_sql="set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_play_user_chapter_count_1d partition (dt)
select
      uid,
       id,
       chapter_name,
     course_name,
       count(1)  play_count_1d,
       avg(play_sec) avg_duration_sec_1d,
       count(distinct uid) uv_count_1d,
        dt
from dwd_videoplay_full
where dt <='2022-06-14'
group by chapter_name,uid,id,dt,course_name;"

dws_trffic_category_user_videoplay_1d="
insert overwrite table  dws_trffic_category_user_videoplay_1d partition(dt)
select
    category_id,
    category_name,
    user_id,
    count(1),
    vi.dt
from (
         select
             uid user_id,
            chapter_id,
            dt
         from dwd_videoplay_full
         where dt <= '2022-06-14'
     )vi left join (
    select
        category_id,
        category_name,
        chapter_id,
        dt
    from dim_chapter_full
    where dt = '2022-06-14'
) ch on vi.chapter_id = ch.chapter_id
group by category_id, category_name, user_id,vi.dt;
"

dws_trffic_subject_user_videoplay_1d="
insert overwrite table dws_trffic_subject_user_videoplay_1d partition (dt)
select
    subject_id,
    subject_name,
    category_id,
    category_name,
    user_id,
    count(1),
    vi.dt
from (
         select
             uid user_id,
            chapter_id,
            dt
         from dwd_videoplay_full
         where dt <= '2022-06-14'
     )vi left join (
    select
        category_id,
        category_name,
        subject_id,
        subject_name,
        chapter_id,
        dt
    from dim_chapter_full
    where dt = '2022-06-14'
) ch on vi.chapter_id = ch.chapter_id
group by subject_id, subject_name, category_id, category_name, user_id,vi.dt;
"

dws_trffic_course_user_videoplay_1d="
insert overwrite table dws_trffic_course_user_videoplay_1d partition (dt)
select
    course_id,
    course_name,
    subject_id,
    subject_name,
    category_id,
    category_name,
    user_id,
    count(1),
    vi.dt
from (
         select
            uid user_id,
            chapter_id,
            dt
         from dwd_videoplay_full
         where dt <= '2022-06-14'
     )vi left join (
    select
        course_id,
        chapter_id,
        course_name,
        category_id,
        category_name,
        subject_id,
        subject_name,
        dt
    from dim_chapter_full
    where dt = '2022-06-14'
) ch on vi.chapter_id = ch.chapter_id
group by course_id, course_name, subject_id, subject_name, category_id, category_name, user_id,vi.dt;
"

dws_course_paper_test_paper_1d_sql="
insert overwrite table dws_course_paper_test_paper_1d partition (dt)
select paper_id,
       sum(paper_score)        total_score_1d,
       avg(paper_score)        avg_score_1d,
       sum(duration_sec)       total_duration_sec_1d,
       avg(duration_sec)       avg_duration_sec_1d,
       count(distinct user_id) total_user_1d,
       dt
from (
         select distinct paper_id,
                         user_id,
                         paper_score,
                         duration_sec,
			dt
         from dwd_test_action_inc
         where dt<='2022-06-14'
     ) t
group by paper_id,dt;
"
dws_course_course_test_course_1d_sql="
insert overwrite table dws_course_course_test_course_1d partition (dt)
select course_id,
       sum(ta.paper_id)        total_course_score_1d,
       avg(paper_score)        avg_course_score_1d,
       sum(duration_sec)       total_course_duration_sec_1d,
       avg(duration_sec)       avg_course_duration_sec_1d,
       count(distinct user_id) total_course_user_1d,
	   dt
from (
         select course_id,
                paper_id
         from dim_paper_full
         where dt <= '2022-06-14'
         group by course_id, paper_id
     ) pf
         join (
    select distinct paper_id,
                    user_id,
                    paper_score,
                    duration_sec,
			dt
    from dwd_test_action_inc
    where dt <= '2022-06-14'
) ta
              on pf.paper_id = ta.paper_id
group by course_id,dt;
"

dws_paper_score_page_1d_sql="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_paper_score_page_1d partition (dt)
select paper_id,paper_title,page_score,count(1) paper_score_1d,dt
from (select user_id,
             t1.paper_id,
             t2.paper_title,
             case
                 when paper_score >= 90 then '90-100'
                 when paper_score >= 80 then '80-90'
                 when paper_score >= 70 then '70-80'
                 when paper_score >= 60 then '60-70'
                 when paper_score >= 50 then '50-60'
                 when paper_score >= 40 then '40-50'
                 when paper_score >= 30 then '30-40'
                 when paper_score >= 20 then '20-30'
                 when paper_score >= 10 then '10-20'
                 when paper_score >= 0 then '0-10'
                 end      page_score,
             t1.dt
      from dwd_test_action_inc t1
               join ods_test_paper_full t2 on t1.paper_id = t2.id
      where t1.dt <= '2022-06-14'
         or t2.dt <= '2022-06-14'
     )t3
group by paper_id,paper_title,page_score,dt;
"
dws_question_accuracy_1d_sql="
insert into table dws_question_accuracy_1d partition (dt)
select question_id,
     count(if((is_correct=1),1,null)) / count(1)   question_accuracy_1d,dt
from dwd_test_action_inc
where dt <= '2022-06-14'
group by question_id,dt;
"

dws_course_order_1d="
insert overwrite table dws_course_order_1d partition(dt)
select
    course_id,
    course_name,
    count(distinct(order_id)),
    sum(origin_amount ),
    sum(coupon_reduce),
    sum(final_amount ),
    count(distinct (user_id)),
	dt
from dwd_trade_order_detail_inc
where  dt<='2022-06-14'
group by course_id,course_name,dt;
"

dws_course_review_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_course_review_1d partition(dt)
select t1.course_id,
       user_review_sum_1d,
       user_review_count_1d,
       user_review_sum_1d/user_review_count_1d user_avg_review_1d,
      good_review_count_1d/user_review_count_1d positive_ratings_1d,
      dt
from (select
          course_id,
          dt,
          sum(review_stars ) user_review_sum_1d,
          count(distinct (user_id)) user_review_count_1d,
          count(if(review_stars=="5",1,0)) good_review_count_1d
      from dwd_course_evaluate_inc
      where  dt<='2022-06-14'
      group by course_id,dt
     ) t1;
"

dws_traffic_session_page_order_1d_sql="insert overwrite table dws_traffic_session_page_order_1d partition(dt='2022-06-14')
select t1.session_id,
       mid_id,
       brand,
       model,
       operate_system,
       version_code,
       t1.channel,
       page_item_type,
       t1.user_id,
       order_status,
       sum(final_amount)
from  dwd_traffic_page_view_inc t1
         left join dwd_payment_info_inc t2
                    --两个事实表关联必须保证事实发生在同一天，根据用户id和用户当天下单的行为，必须保证日期是同一天
                   on t1.user_id = t2.user_id and t1.dt =t2.dt
where t1.dt = '2022-06-14'
  and t1.session_id is not null
  and t2.session_id is not null
group by channel, t1.session_id, mid_id, brand, model, operate_system, version_code,t1.user_id,order_status,page_item_type
;"

dws_traffic_session_page_view_1d_sql="insert overwrite table dws_traffic_session_page_view_1d partition(dt='2022-06-14')
select
    session_id,
    mid_id,
    brand,
    model,
    operate_system,
    version_code,
    channel,
    sum(during_time) during_time_1d,
    count(*) page_count_1d
from dwd_traffic_page_view_inc
where dt='2022-06-14' and session_id is not null
group by session_id,mid_id,brand,model,operate_system,version_code,channel;"

dws_trade_course_category_order_1d_sql="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_trade_course_category_order_1d partition (dt)
select
    category_id,
    category_name,
    count(distinct order_id) order_count_1d,
    count(distinct user_id) order_num_1d,
    sum(origin_amount) order_original_amount_1d,
    sum(nvl(coupon_reduce,0.0)) coupon_reduce_amount_1d,
    sum(final_amount) order_total_amount_1d,
    dt
from dwd_trade_order_detail_inc
where dt <= '2022-06-14'
group by dt, category_id, category_name;
"
dws_trade_subject_course_order_1d_sql="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_trade_subject_course_order_1d partition(dt)
select
    subject_id,
    subject_name,
    count(distinct order_id) order_count_1d,
    count(distinct user_id) order_num_1d,
    sum(origin_amount) order_original_amount_1d,
    sum(nvl(coupon_reduce,0.0)) coupon_reduce_amount_1d,
    sum(final_amount) order_total_amount_1d,
    dt
from dwd_trade_order_detail_inc
where dt <= '2022-06-14'
group by dt, subject_id, subject_name;
"


dws_trade_all_order_1d_sql="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_trade_all_order_1d partition (dt)
    select count(distinct user_id) order_users_num_1d,
           sum(order_count_1d) order_count_1d,
           sum(order_total_amount_1d) order_total_amount_1d,
           dt
    from (
            select user_id,
            count(order_id) order_count_1d,
            sum(final_amount) order_total_amount_1d,
            dt
            from dwd_trade_order_detail_inc
            where dt <= '2022-06-14'
            group by user_id,dt
             )t1
group by dt;
"

dws_trade_province_order_1d_sql="
insert overwrite table dws_trade_province_order_1d partition (dt)
    select province_id,
           province_name,
           count(distinct user_id) order_users_num_1d,
           sum(order_count_1d) order_count_1d,
           sum(order_total_amount_1d) order_total_amount_1d,
           dt
    from (
            select province_id,
                   province_name,
                   user_id,
            count(order_id) order_count_1d,
            sum(final_amount) order_total_amount_1d,
            dt
            from dwd_trade_order_detail_inc
            where dt <= '2022-06-14'
            group by user_id,province_id,province_name,dt
             )t1
group by province_id,province_name,dt;
"

dws_trade_user_payment_1d="
insert overwrite table dws_trade_user_payment_1d partition(dt)
select
    user_id,
    count(distinct(order_id)),
    count(distinct(course_id)),
    sum(final_amount),
    dt
from dwd_trade_order_detail_inc
WHERE dt ='2022-06-14'
group by user_id,dt;
"

dws_trade_user_order_1d="
insert overwrite table dws_trade_user_order_1d partition(dt)
select
    user_id,
    count(distinct(order_id)) ,
    sum(origin_amount),
    sum(nvl(coupon_reduce,0)),
    sum(final_amount),
    dt
from dwd_trade_order_detail_inc
where dt='2022-06-14'
group by user_id,dt;
"

dws_user_age_order_1d="
insert overwrite table dws_user_age_order_1d partition (dt)
select
    age_group,
    order_count order_count_1d,
    dt
from (
         select distinct dt,
                         age_group,
                         count(user_id) over (partition by age_group) order_count
         from (
                  select user_id,
                         dt,
                         case

                             when age >= 10 and age < 20 then '青少年'
                             when age >= 20 and age < 30 then '青年'
                             when age >= 30 and age < 40 then '中年'
                             when age >= 40 and age < 50 then '大龄中年'
                             when age >= 50 then '老年'
                              else '少年'
                             end age_group
                  from (
                           select user_id,
                                  dt,
                                  date_format(birthday, 'yyyy-MM-dd'),
                                  year('2022-06-14') - year(date_format(birthday, 'yyyy-MM-dd')) age
                           from dwd_trade_order_detail_inc
						   where dt='2022-06-14'

                       ) t1
              ) t2
    )
t3;
"

dws_trade_user_cart_add_1d="
INSERT OVERWRITE TABLE dws_trade_user_cart_add_1d PARTITION (dt)
SELECT user_id,
       count(*) cart_add_count_1d,
       dt
FROM dwd_trade_cart_add_inc
WHERE dt ='2022-06-14'
GROUP BY user_id , dt;
"

dws_traffic_page_visitor_page_view_1d="
INSERT OVERWRITE TABLE dws_traffic_page_visitor_page_view_1d PARTITION (dt)
SELECT
       mid_id,
       page_id,
       sum(during_time) during_time_1d,
       count(*) view_count_1d,
dt
FROM  dwd_traffic_page_view_inc
WHERE dt ='2022-06-14'
GROUP BY mid_id,page_id,dt;

"


case $1 in
      "dws_traffic_user_course_videoplay_1d" )
          /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_traffic_user_course_videoplay_1d;"
      ;;
      "dws_traffic_user_chapter_videoplay_1d" )
          /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_traffic_user_chapter_videoplay_1d;"
      ;;
        "dws_play_user_course_count_1d" )
          /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_play_user_course_count_1d_sql;"
      ;;
          "dws_play_user_chapter_count_1d" )
          /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_play_user_chapter_count_1d_sql;"
      ;;
      "dws_trffic_category_user_videoplay_1d")
      /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trffic_category_user_videoplay_1d;"
      ;;
      "dws_trffic_subject_user_videoplay_1d")
        /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trffic_subject_user_videoplay_1d;"
      ;;
      "dws_trffic_course_user_videoplay_1d")
        /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trffic_course_user_videoplay_1d;"
      ;;
          "dws_course_paper_test_paper_1d")
        /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_course_paper_test_paper_1d_sql;"
      ;;
      "dws_course_course_test_course_1d")
        /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_course_course_test_course_1d_sql;"
      ;;
   "dws_paper_score_page_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_paper_score_page_1d_sql;"
;;
"dws_question_accuracy_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_question_accuracy_1d_sql;"
	;;
    "dws_course_order_1d" )
       /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_course_order_1d;"
    ;;
  "dws_course_review_1d" )
      /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_course_review_1d;"
    ;;
  "dws_traffic_session_page_order_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_traffic_session_page_order_1d_sql;"
;;
"dws_traffic_session_page_view_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_traffic_session_page_view_1d_sql;"
;;
"dws_trade_all_order_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trade_all_order_1d_sql;"
;;
"dws_trade_province_order_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trade_province_order_1d_sql;"
;;
"dws_trade_course_category_order_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trade_course_category_order_1d_sql;"
;;
"dws_trade_province_order_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trade_subject_course_order_1d_sql;"
;;

"dws_interaction_sku_favor_add_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trade_user_payment_1d"
;;
"dws_tool_user_coupon_coupon_used_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trade_user_order_1d"

;;

"dws_trade_province_order_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_user_age_order_1d"

;;

"dws_trade_user_cart_add_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trade_user_cart_add_1d"

;;
"dws_trade_user_order_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_traffic_page_visitor_page_view_1d"
;;

"all")
     /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trade_course_category_order_1d_sql$dws_trade_subject_course_order_1d_sql$dws_trade_all_order_1d_sql$dws_trade_province_order_1d_sql$dws_traffic_session_page_order_1d_sql$dws_traffic_session_page_view_1d_sql$dws_course_order_1d $dws_course_review_1d$dws_paper_score_page_1d_sql$dws_question_accuracy_1d_sql$dws_course_paper_test_paper_1d_sql$dws_course_course_test_course_1d_sql$dws_trffic_category_user_videoplay_1d$dws_trffic_subject_user_videoplay_1d$dws_trffic_course_user_videoplay_1d$dws_play_user_chapter_count_1d_sql$dws_traffic_user_course_videoplay_1d$dws_traffic_user_chapter_videoplay_1d$dws_play_user_course_count_1d_sql"
     /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;
	$dws_trade_user_payment_1d;
	$dws_trade_user_order_1d;
	$dws_user_age_order_1d;
	$dws_trade_user_cart_add_1d;
	$dws_traffic_page_visitor_page_view_1d;"
;;
esac


